/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Deprecated;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

/**
 *  增删改操作
 * @author lcfgly
 */
@Deprecated
@Repository
public class DbIPD<T>{
    
    /**
     * 数据源
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 添加数据
     * @param object
     * @param table
     * @return
     * @throws SQLException 
     */
    public int  insert(String table,T object) throws SQLException
    {
        StringBuffer key=new StringBuffer();
        StringBuffer value=new StringBuffer();
        Object[] params=setParams(object, key, value);
        String sql="INSERT INTO "+table+" ( "+key.substring(0,key.length()-1)+" ) VALUE ( "+value.substring(0,value.length()-1)+" )";
        try{
            return jdbcTemplate.update(sql, params);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 更新数据
     * @param table
     * @param object
     * @param keyname
     * @param key
     * @return
     * @throws SQLException 
     */
    public int update(String table,T object,String keyname,Object key) throws SQLException
    {
        StringBuffer sql=new StringBuffer();
        Object[] params=setUpdateparam(object, sql);
        String sqle="UPDATE "+table+" SET "+sql.substring(0, sql.length()-1)+" WHERE "+keyname+"= ?";
        params[params.length-1]=key;
        try{
            return jdbcTemplate.update(sqle, params);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 删除
     * @param table
     * @param keyname
     * @param key
     * @throws SQLException
     * @return 
     */
    public int delete(String table,String keyname,Object key) throws SQLException
    {
        String sql="DELETE FROM "+table+" WHERE "+keyname+" = ? ";
        try{
            return jdbcTemplate.update(sql, new Object[]{key});
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 批量删除
     * @param table
     * @param keyname
     * @param keys
     * @return
     * @throws SQLException 
     */
    public int delete_list(String table,String keyname,Object[] keys) throws SQLException
    {
        String x="";
        for (Object object : keys) {
            x+="?,";
        }
        x=x.substring(0, x.length()-1);
        String sql="DELETE FROM "+table+" WHERE "+keyname+" IN ( "+x+")";
        try{
            return jdbcTemplate.update(sql, keys);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 批量插入
     * @param table
     * @param args
     * @throws SQLException  
     * @return 
     */
    public int[] insert_list(String table,List<T> args) throws SQLException
    {
        StringBuffer key=new StringBuffer();
        StringBuffer value=new StringBuffer();
        List<Object[]> params=new ArrayList<Object[]>();
        for(T object:args)
        {
            key.setLength(0);
            value.setLength(0);
            params.add(setParams(object, key, value));
        }
        String sql="INSERT INTO "+table+" ( "+key.substring(0,key.length()-1)+" ) VALUE ( "+value.substring(0,value.length()-1)+" )";
        try{
           return  jdbcTemplate.batchUpdate(sql, params);
        }catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 插入返回自增主键
     * @param table
     * @param object
     * @return
     * @throws SQLException 
     */
    public int insertBackID(String table,T object)throws  SQLException
    {
        StringBuffer key=new StringBuffer();
        StringBuffer value=new StringBuffer();
        KeyHolder keyHolder = new GeneratedKeyHolder();
        final Object[] params=setParams(object, key, value);
        final String sql="INSERT INTO "+table+" ( "+key.substring(0,key.length()-1)+" ) VALUE ( "+value.substring(0,value.length()-1)+" )";
        try {
            jdbcTemplate.update(new PreparedStatementCreator() {
                            @Override
                            public PreparedStatement createPreparedStatement(Connection con)throws SQLException {
                                            PreparedStatement ps = jdbcTemplate.getDataSource().getConnection().prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                                            for(int i = 0;i<params.length;i++){
                                                    ps.setObject(i+1, params[i]);
                                            }
                                        return ps;
                            }
            }, keyHolder);
        } catch (DataAccessException e) {
            throw new SQLException(e.getMessage());
        }
        return keyHolder.getKey().intValue();
    }
    
    
    /**
     * 设置从对象中获取所需数据
     * @param object
     * @param key
     * @param value
     * @return 
     */
    private Object[] setParams(T object,StringBuffer key,StringBuffer value)
    {
        Class c=object.getClass();
        Field[] f=c.getDeclaredFields();
        ArrayList<Object> fs=new ArrayList<Object>();
        for(Field f1 : f)
        {
            String paramname=f1.getName();
            if(!paramname.equals("id") && !paramname.equals("serialVersionUID"))
            {
                f1.setAccessible(true);
                try {
                    Object param=f1.get(object);
                    key.append(paramname);
                    key.append(",");
                    value.append("?,");
                    fs.add(param);
                } catch (IllegalArgumentException ex) {
                    Logger.getLogger(DbIPD.class.getName()).log(Level.SEVERE, null, ex);
                } catch (IllegalAccessException ex) {
                    Logger.getLogger(DbIPD.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        Object[] params=new Object[fs.size()];
        int i=0;
        for (Object object1 : fs) {
            params[i++]=object1;
        }
        return params;
    }

    
    /**
     * 设置UPDATE数据
     * @param object 取值的对象（遍历对象能set的所有值）
     * @param sql  查询sql数据字符串
     * @param objects 对应数据存放对象
     */
    private  Object[] setUpdateparam(T object,StringBuffer sql)
    {
        Class c=object.getClass();
        Field[] f=c.getDeclaredFields();
        ArrayList<Object> fs=new ArrayList<Object>();
        for(Field f1 : f)
        {
            String paramname=f1.getName();
            if(!paramname.equals("id") && !paramname.equals("serialVersionUID"))
            {
                f1.setAccessible(true);
                try {
                    Object param=f1.get(object);
                    sql.append( paramname);
                    sql.append("=?,");
                    fs.add(param);
                } catch (IllegalArgumentException ex) {
                    Logger.getLogger(DbIPD.class.getName()).log(Level.SEVERE, null, ex);
                } catch (IllegalAccessException ex) {
                    Logger.getLogger(DbIPD.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        Object[] params=new Object[fs.size()+1];
        int i=0;
        for (Object object1 : fs) {
            params[i++]=object1;
        }
        return params;
    }

}
